package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;

import control.ConnectionManage;

import db.parking.beans.Orders;
import db.parking.beans.Sms;

/**
 * AdminSms manages all requests From,To table SMS
 * @author Operations
 *
 */
public class AdminSMS {

	private static Connection conn = ConnectionManage.getInstance().getConnection();

	
	/**
	 * Insert new row into DB table SMS
	 * @param bean
	 * @param message
	 * @param curr
	 * @return
	 * @throws Exception
	 */
	public static boolean insert(Sms bean,String message,Timestamp curr) throws Exception {
	    //order id is autoIncremented on Insert

			String sql = "INSERT into sms (cusID, oID, message, timeSent, status) " +
					"VALUES (?, ? ,? ,? ,? )";
			ResultSet keys = null;
			try (
					PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					) {
				
				stmt.setString(1, bean.getCusID());
				stmt.setInt(2, bean.getoID());
				stmt.setString(3, message);
				stmt.setTimestamp(4,curr);
				stmt.setInt(5, bean.getStatus());
				int affected = stmt.executeUpdate();
				
				if(affected == 1){
					keys = stmt.getGeneratedKeys();
					keys.next();
					int newKey = keys.getInt(1);
				}
				else{
					System.err.println("No rows affected");
					return false;
				}
				
			} catch (SQLException e) {
				System.err.println(e);
				return false;
			} finally{
				if(keys != null)
					keys.close();
			}
			return true;
			
		}
	
	/**
	 * Functions returns true if SMS was sent
	 * @param oid
	 * @return
	 * @throws SQLException
	 */
	public static boolean smsSent(int oid) throws SQLException {
		String sql = "SELECT * FROM sms WHERE oid = ? and status =1";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, oid);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();		
		
			if (totalRows > 0)
				return true;

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return false;
	
	}
	
	/**
	 * Functions returns true if SMS exists
	 * @param oid
	 * @return
	 * @throws SQLException
	 */
	public static boolean smsExists(int oid) throws SQLException {
		String sql = "SELECT * FROM sms WHERE oid = ?";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, oid);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();		
		
			if (totalRows > 0)
				return true;

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return false;
	
	}
	
	/**
	 * Functions returns true if a reply has been receieved from the Late user
	 * @param oid
	 * @return
	 * @throws SQLException
	 */
	public static boolean msgSent(int oid) throws SQLException {
		String sql = "SELECT * FROM sms WHERE oid = ? AND staus > 2";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, oid);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();		
		
			if (totalRows > 0)
				return true;

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return false;
	
	}
	
	/**
	 * Go to status 4 after sms was confirmed by user
	 * @param oid
	 * @return
	 * @throws SQLException
	 */
	public static boolean smsFine(int oid) throws SQLException {
		String sql = "SELECT * FROM sms WHERE oid = ? AND status = 4";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, oid);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();		
		
			if (totalRows > 0)
				return true;

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return false;
	
	}
	
	
	/**
	 * Functions updates current status of sms which was sent to user
	 * @param oid
	 * @param status
	 * @return
	 * @throws Exception
	 */
	public static boolean update(int oid,int status) throws Exception {

		String sql =
				"UPDATE sms SET " +
				"status = ? " +
				"WHERE oID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,status);
			stmt.setInt(2,oid);

			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	/**
	 * Functions returns an array of rows which were answered by the user
	 * @return
	 * @throws SQLException
	 */
	public static Sms[] getSmsAnswer() throws SQLException {
		String sql = "SELECT * FROM sms WHERE status = 2";
		
		ResultSet rs = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				) {			

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Sms bean[] = new Sms[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Sms();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setCusID(rs.getString("cusID"));
				bean[rowCount].setoID(rs.getInt("oID"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	

	
}
